package xf.gradle.project.database.access;

import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.util.*;
import io.vertx.core.json.JsonArray;
import org.junit.Test;

public class Access {
    public static void main(String[] args) throws IOException {
        getPrimaryKeysLocal();
//        localFile();

    }

    /**
     * 读取指定表格
     * @throws IOException
     */
//    public static void getAccessDataTable() throws IOException {
//        Table table = DatabaseBuilder.open(new File("C:/Users/admin/Documents/test1.accdb")).getTable("");
//        for(Row row : table) {
//            System.out.println("--ID--" + row.get("CollectID")+"--Name--" + row.get("CollectName"));
//        }
//    }


    private static void ipFile() {
        try {

            Connection con = getConnection();
            Statement sta = con.createStatement();
            ResultSet resultSet = sta.executeQuery("select * from test");//demoTable为access数据库中的一个表名
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<Map<String, Object>> list = new ArrayList<>();
            while (resultSet.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < columnCount; i++) {
                    String columnName = metaData.getColumnName(i + 1);
                    Object object = resultSet.getObject(i + 1);
                    map.put(columnName, object);
                }
                list.add(map);
            }
            System.out.println(list);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static void getTables(){
        try {

            Connection conn = getConnection();

            Statement stmt=conn.createStatement();

            DatabaseMetaData  dbmd=conn.getMetaData();
            ResultSet  rs=dbmd.getTables(null,null,"test1",null);
            while(rs.next()){

                ResultSetMetaData m = rs.getMetaData();
                for(int i = 1;i<=25;i++){

                    System.out.println("table-name:  "+rs.getString(i));
                }
                System.out.println("");
//                System.out.println("table-name:  "+rs.getString(2));
//                System.out.println("table-name:  "+rs.getString(3));
//                System.out.println("table-name:  "+rs.getString(4));
//                System.out.println("table-name:  "+rs.getString(5));
            }

////            ResultSet  rs=dbmd.getPrimaryKeys(null,null,"test1");
//            while(rs.next()){
//                System.out.println("path:  "+rs.getString(1));
//                System.out.println(":  "+rs.getString(2));
//                System.out.println("table-name:  "+rs.getString(3));
//                System.out.println("table-type:  "+rs.getString(4));
//                System.out.println("table-:  "+rs.getString(5));
//
//            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static void getPrimaryKeysLocal(){
        try {

            Connection conn = getLocalConnection(); //必须
            DatabaseMetaData  dbmd=conn.getMetaData();
            ResultSet  rs=dbmd.getPrimaryKeys(null,null,"test");
            while(rs.next()){
                for(int i = 1;i<=6;i++){

                    System.out.println("table-name:  "+rs.getString(i));
                }
                System.out.println("");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static void getPrimaryKeys(){
        try {

            Connection conn = getConnection(); //必须
            DatabaseMetaData  dbmd=conn.getMetaData();
            ResultSet  rs=dbmd.getPrimaryKeys(null,null,"test");
            while(rs.next()){
                for(int i = 1;i<=6;i++){

                    System.out.println("table-name:  "+rs.getString(i));
                }
                System.out.println("");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static void getColums(){
        try {

            Connection conn = getLocalConnection();

            Statement stmt=conn.createStatement();

            DatabaseMetaData  dbmd=conn.getMetaData();
//            ResultSet  rs=dbmd.getTables(null,null,"%",null);
           ResultSet  rs=dbmd.getColumns(null,null,"test",null);
//           ResultSet  rs=dbmd.getColumns(null,null,"%",null);
            while(rs.next()){

                ResultSetMetaData m = rs.getMetaData();
                for(int i = 1;i<=25;i++){

                    System.out.println("table-name:  "+rs.getString(i));
                }
                System.out.println("");
//                System.out.println("table-name:  "+rs.getString(2));
//                System.out.println("table-name:  "+rs.getString(3));
//                System.out.println("table-name:  "+rs.getString(4));
//                System.out.println("table-name:  "+rs.getString(5));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static void getTableInfo() {
        try {

            Connection con = getLocalConnection();

            Statement st = null;
            ResultSet rs = null;
            st = con.createStatement();

            rs = st.executeQuery("select * from test");
            ResultSetMetaData data = rs.getMetaData();
            while (rs.next()) {
                for (int i = 1; i <= data.getColumnCount(); i++) {
                    //获得所有列的数目及实际列数
                    int columnCount = data.getColumnCount();

                    //获得指定列的列名
                    String columnName = data.getColumnName(i);

                    //获得指定列的列值
                    String columnValue = rs.getString(i);

                    //获得指定列的数据类型
                    int columnType = data.getColumnType(i);

                    //获得指定列的数据类型名
                    String columnTypeName = data.getColumnTypeName(i);

                    //所在的Catalog名字
                    String catalogName = data.getCatalogName(i);

                    //对应数据类型的类
                    String columnClassName = data.getColumnClassName(i);

                    //在数据库中类型的最大字符个数
                    int columnDisplaySize = data.getColumnDisplaySize(i);

                    //默认的列的标题
                    String columnLabel = data.getColumnLabel(i);

                    //获得列的模式
                    String schemaName = data.getSchemaName(i);

                    //某列类型的精确度(类型的长度)
                    int precision = data.getPrecision(i);

                    //小数点后的位数
                    int scale = data.getScale(i);

                    //获取某列对应的表名
                    String tableName = data.getTableName(i);

                    // 是否自动递增
                    boolean isAutoInctement = data.isAutoIncrement(i);

                    //在数据库中是否为货币型
                    boolean isCurrency = data.isCurrency(i);

                    //是否为空
                    int isNullable = data.isNullable(i);

                    //是否为只读
                    boolean isReadOnly = data.isReadOnly(i);

                    //能否出现在where中
                    boolean isSearchable = data.isSearchable(i);

                }

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }



    public static Connection getConnection() {
        try {
            Class.forName("org.objectweb.rmijdbc.Driver");
//            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//            String dbURL = "jdbc:ucanaccess://" +
//                    "C:\\Users\\f1334185\\Documents\\数据库4.accdb";
            String dbURL = "jdbc:rmi://192.168.1.83:6000/jdbc:odbc:test1";
            Properties pro = new Properties();
            pro.put("user", "admin");
            pro.put("password", "123456");
            return DriverManager.getConnection(dbURL,pro);
        } catch (Exception e) {
            System.out.println("AccessDB connection fail");
            e.printStackTrace();
        }
        return null;
    }


    public static Connection getLocalConnection() {
        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
//            String dbURL = "jdbc:ucanaccess://" +
//                    "C:\\Users\\f1334185\\Documents\\数据库4.accdb";
            String dbURL = "jdbc:ucanaccess://C:\\Users\\admin\\Documents\\test1.accdb";
            Properties pro = new Properties();
            pro.put("user", "admin");
            pro.put("password", "123456");
            pro.put("jackcessopener", "xf.gradle.project.database.access.JackcessOpener");
            return DriverManager.getConnection(dbURL, pro);
        } catch (Exception e) {
            System.out.println("AccessDB connection fail");
            e.printStackTrace();
        }
        return null;
    }

    private static void localFile(){
        try {

            Connection con = getLocalConnection();
            Statement sta = con.createStatement();
            ResultSet resultSet = sta.executeQuery("SELECT  top 100000 id,username,sex FROM `test`");//demoTable为access数据库中的一个表名
//            ResultSet resultSet = sta.executeQuery("select top 4 * from test where ID not in (select top 2 ID from test order by ID ) order by ID");//demoTable为access数据库中的一个表名
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<Map<String, Object>> list = new ArrayList<>();
            while (resultSet.next()){
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < columnCount; i++) {
                    String columnName = metaData.getColumnName(i + 1);
                    Object object = resultSet.getObject(i+1);
                    map.put(columnName, object);
                }
                list.add(map);
            }
            System.out.println(list);
        } catch (SQLException e) {
            e.printStackTrace();
        }


    }



    /**
     * !access关闭，负责没权限
     * ok
     * @return
     */
    public static Connection localFileNoPWD() {
        try {
            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
            String url = "jdbc:ucanaccess://C:/Users/admin/Documents/test1.accdb";
//            String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\\Users\\admin\\Documents\\test.accdb";
//            Connection con = DriverManager.getConnection(url);//没有用户名和密码的时候直接为空
            Connection con = DriverManager.getConnection(url,"","");//没有用户名和密码的时候直接为空
            Statement sta = con.createStatement();
            ResultSet rst = sta.executeQuery("select * from test1");//demoTable为access数据库中的一个表名
            ResultSetMetaData metaData = rst.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<Map<String, Object>> list = new ArrayList<>();
            while (rst.next()){
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < columnCount; i++) {
                    String columnName = metaData.getColumnName(i + 1);
                    Object object = rst.getObject(i+1);
                    map.put(columnName, object);
                }
                list.add(map);
            }
            System.out.print(new JsonArray(list).toString());

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Test
    public void test1() throws Exception{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        String dburl = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=//192.168.51.95/share/att2000.mdb";// 此为NO-DSN方式
        Connection conn = DriverManager.getConnection(dburl);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("select top 5  * from checkinout");
        while (rs.next()) {
            System.out.println(rs.getInt(1) + rs.getDate(2).toString() + rs.getString(3));
        }
        rs.close();
        stmt.close();
        conn.close();
    }


    /**
     * 通过rmi协议，远程连接access数据库；需要在access数据库所在服务器上运行一个rmi服务
     * server端运行命令：java -jar E:\store\all\RmiJdbc\dist\lib\RmiJdbc.jar -port 8899
     *
     * @throws Exception
     */
    @Test
    public void test2remoteConnAccess() throws Exception{
        try {
            // Register RmiJdbc Driver in jdbc DriverManager
            // On some platforms with some java VMs, newInstance() is
            // necessary...
            Class.forName("org.objectweb.rmijdbc.Driver").newInstance();
//            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver").newInstance();

            // Test with InstantDB java database engine
            // See http://www.lutris.com/products/instantDB/index.html
            // for info & download
            String url = "jdbc:odbc:test32";
            // RMI host will point to local host
//			String rmiHost = "//192.168.51.94:8899";
            String rmiHost = "//localhost:6000";

//			String rmiHost = "//192.168.4.31:8899";

            // RmiJdbc URL is of the form:
            // jdbc:rmi://<rmiHostName[:port]>/<jdbc-url>
            java.sql.Connection c = DriverManager.getConnection("jdbc:rmi:"
                    + rmiHost + "/" + url);

            String querySql = "select top 3  * from checkinout where datediff('s',?,checktime)>=0";
            java.sql.PreparedStatement st = c.prepareStatement(querySql);
            st.setString(1, "2016/04/20 00:00:00");
            java.sql.ResultSet rs = st.executeQuery();

            java.sql.ResultSetMetaData md = rs.getMetaData();
            while (rs.next()) {
                System.out.print("\nTUPLE: | ");
                for (int i = 1; i <= md.getColumnCount(); i++) {
//					System.out.print(rs.getString(i) + " | ");
                    System.out.print(rs.getObject(i) + " | ");
                    System.out.print(md.getColumnName(i) + " | ");
                }
            }
            rs.close();
            st.close();
            c.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
